#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pymysql as mysql
import json
from flask import Flask, render_template, request

# 导入flask_wtf提供的表单
# from flask_wtf import FlaskForm
from wtforms import Form

# 导入表单类提供的字段
from wtforms import StringField, SelectField

# 导入表单类提供的验证函数
from wtforms.validators import DataRequired

app = Flask(__name__)
db = mysql.connect(host='127.0.0.1', port=3306, user="root", passwd="mysql", db="testdb", charset="utf8mb4")
db.autocommit(True)
db.ping(True)
c = db.cursor()
app.config['SECRET_KEY'] = '123456'
tmp_time = 0


class DataForm(Form):
	tag_id = SelectField(render_kw={"onchange": "selected()"})
	ip = StringField(validators=[DataRequired()], render_kw={"id": "ip"})


@app.route("/", methods=["GET", "POST"])
def index():
	return render_template("index.html")


@app.route("/user/memory/")
def mem_list():
	form = DataForm()
	sql = 'select distinct host,ip from mem'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("memory.html", form=form)


@app.route("/memory")
def memory():
	sql = "select time,free,used,swap_total,swap_used,host from mem where host=%s and ip=%s"
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = {}
	free = []
	used = []
	swap_total = []
	swap_used = []
	for i in c.fetchall():
		free.append([i[0] * 1000, i[1], i[5]])
		used.append([i[0] * 1000, i[2], i[5]])
		swap_total.append([i[0] * 1000, i[3], i[5]])
		swap_used.append([i[0] * 1000, i[4], i[5]])
	arr['free'] = free
	arr['used'] = used
	arr['swap_total'] = swap_total
	arr['swap_used'] = swap_used
	return json.dumps(arr)


@app.route("/user/disk/")
def disk_list():
	form = DataForm()
	sql = 'select distinct host,ip from disk'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("disk.html", form=form)


@app.route("/disk")
def disk():
	sql = 'select mountpoint,free,total,host from disk where host=%s and ip=%s and  nowtime between date_add(sysdate(),interval -10 day) and (sysdate())'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = {}
	free = []
	total = []
	for i in c.fetchall():
		free.append([i[0], i[1], i[3]])
		total.append([i[0], i[2], i[3]])
	arr['free'] = free
	arr['total'] = total
	return json.dumps(arr)


@app.route("/user/cpu/")
def cpu_list():
	form = DataForm()
	sql = 'select distinct host,ip from cpu'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("cpu.html", form=form)


@app.route("/cpu")
def cpu():
	sql = 'select time,percent,host from cpu where host=%s and ip=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = []
	for i in c.fetchall():
		arr.append([i[0] * 1000, i[1], i[2]])
	return json.dumps(arr)


@app.route("/user/network/")
def network_list():
	form = DataForm()
	sql = 'select host,ip,trim(group_concat(network)) as network from (select distinct host,ip,network from network) a group by host'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[2], v[0]) for v in c.fetchall()]
	return render_template("network.html", form=form)


@app.route("/network")
def network():
	sql = 'select time,input,output,host,network from network where host=%s and network=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('network')])
	arr = {}
	input = []
	output = []
	for i in c.fetchall():
		input.append([i[0] * 1000, i[1], i[3], i[4]])
		output.append([i[0] * 1000, i[2], i[3], i[4]])
	arr['input'] = input
	arr['output'] = output
	return json.dumps(arr)


@app.route("/user/io/")
def io_list():
	form =DataForm()
	sql = 'select host,ip,trim(group_concat(device)) as device from (select distinct host,ip,device from disk_io) a group by host'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[2], v[0]) for v in c.fetchall()]
	return render_template("disk_io.html", form=form)


@app.route("/io")
def io():
	sql = 'select time,read_kb,write_kb,host,device from disk_io where host=%s and device=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('device')])
	arr = {}
	io_read = []
	io_write = []
	for i in c.fetchall():
		io_read.append([i[0] * 1000, i[1], i[3], i[4]])
		io_write.append([i[0] * 1000, i[2], i[3], i[4]])
	arr['io_read'] = io_read
	arr['io_write'] = io_write
	return json.dumps(arr)


@app.route("/user/tablespace/")
def tablespace_list():
	form = DataForm()
	sql = 'select distinct host,ip from tablespace'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("tablespace.html", form=form)


@app.route("/tablespace")
def tablespace():
	sql = 'select tablespace_name,(max_size-used_size)/1024 as free_size,host from tablespace where host=%s and ip=%s and nowtime between date_add(sysdate(),interval -10 day) and (sysdate())'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = []
	for i in c.fetchall():
		arr.append([i[0], i[1], i[2]])
	return json.dumps(arr)


@app.route("/user/sga/")
def sga_list():
	form = DataForm()
	sql = 'select distinct host,ip from sga'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("sga.html", form=form)


@app.route("/sga")
def sga():
	sql = "select time,sga_buffer_cache,sga_fixed,sga_java_pool,sga_large_pool,sga_log_buffer,sga_shared_pool,host from sga where host=%s and ip=%s"
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = {}
	sga_buffer_cache = []
	sga_fixed = []
	sga_java_pool = []
	sga_large_pool = []
	sga_log_buffer = []
	sga_shared_pool = []
	for i in c.fetchall():
		sga_buffer_cache.append([i[0] * 1000, i[1], i[7]])
		sga_fixed.append([i[0] * 1000, i[2], i[7]])
		sga_java_pool.append([i[0] * 1000, i[3], i[7]])
		sga_large_pool.append([i[0] * 1000, i[4], i[7]])
		sga_log_buffer.append([i[0] * 1000, i[5], i[7]])
		sga_shared_pool.append([i[0] * 1000, i[6], i[7]])
	arr['sga_buffer_cache'] = sga_buffer_cache
	arr['sga_fixed'] = sga_fixed
	arr['sga_java_pool'] = sga_java_pool
	arr['sga_large_pool'] = sga_large_pool
	arr['sga_log_buffer'] = sga_log_buffer
	arr['sga_shared_pool'] = sga_shared_pool
	return json.dumps(arr)


@app.route("/user/sessions/")
def sessions_list():
	form = DataForm()
	sql = 'select distinct host,ip from sessions'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("sessions.html", form=form)


@app.route("/sessions")
def sessions():
	sql = 'select time,sessions,processes,host from sessions where host=%s and ip=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = {}
	sessions = []
	processes = []
	for i in c.fetchall():
		sessions.append([i[0] * 1000, i[1], i[3]])
		processes.append([i[0] * 1000, i[2], i[3]])
	arr['sessions'] = sessions
	arr['processes'] = processes
	return json.dumps(arr)


@app.route("/user/archivelog/")
def archivelog_list():
	form = DataForm()
	sql = 'select distinct host,ip from archivelog'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("archivelog.html", form=form)


@app.route("/archivelog")
def archivelog():
	sql = 'select  distinct time,size,host from archivelog where host=%s and ip=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = []
	for i in c.fetchall():
		arr.append([i[0] * 1000, i[1], i[2]])
	return json.dumps(arr)


@app.route("/user/hit_ratio/")
def hit_ratio_list():
	form = DataForm()
	sql = 'select distinct host,ip from hit_ratio'
	c.execute(sql)
	form.tag_id.choices = [('请选择主机', '请选择主机')]
	form.tag_id.choices += [(v[1], v[0]) for v in c.fetchall()]
	return render_template("hit_ratio.html", form=form)


@app.route("/hit_ratio")
def hit_ratio():
	sql = 'select time,body_ratio,sql_area_ratio,table_procedure_ratio,trigger_ratio, host from hit_ratio where host=%s and ip=%s'
	c.execute(sql, [request.cookies.get('host'), request.cookies.get('ip')])
	arr = {}
	body_ratio = []
	sql_area_ratio = []
	table_procedure_ratio = []
	trigger_ratio = []
	for i in c.fetchall():
		body_ratio.append([i[0] * 1000, i[1], i[5]])
		sql_area_ratio.append([i[0] * 1000, i[2], i[5]])
		table_procedure_ratio.append([i[0] * 1000, i[3], i[5]])
		trigger_ratio.append([i[0] * 1000, i[4], i[5]])
	arr['body_ratio'] = body_ratio
	arr['sql_area_ratio'] = sql_area_ratio
	arr['table_procedure_ratio'] = table_procedure_ratio
	arr['trigger_ratio'] = trigger_ratio
	return json.dumps(arr)


if __name__ == "__main__":
	app.run(host="127.0.0.1", port=5000, debug=True)
